package com.xu.book.dao.impl;

import com.xu.book.bean.Classes;
import com.xu.book.bean.Depart;
import com.xu.book.dao.IClassesDao;
import com.xu.sys.utils.MyDbutils;
import com.xu.sys.utils.PageUtils;
import com.xu.sys.utils.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ClassesDaoimpl implements IClassesDao {
    @Override
    public List<Classes> listPage(PageUtils pageUtils) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "select * from t_classes limit ?,?";
        if(StringUtils.isNotEmpty(pageUtils.getKey())){
            sql = "select * from t_classes where name like '%"+pageUtils.getKey()+"%' or notes like '%"+pageUtils.getKey()+"%' limit ?,? ";
        }
        // 计算 分页开始的位置
        int startNo = pageUtils.getStart();
        try {
            List<Classes> list = queryRunner.query(sql, new
                    ResultSetHandler<List<Classes>>() {
                        @Override
                        public List<Classes> handle(ResultSet resultSet) throws SQLException {
                            // 存储返回结果的容器
                            List<Classes> list = new ArrayList<>();
                            while(resultSet.next()){
                                // 每次循环一次 user 存储一条数据
                                Classes entity = new Classes();
                                entity.setId(resultSet.getInt("id"));
                                entity.setName(resultSet.getString("name"));
                                entity.setNotes(resultSet.getString("notes"));
                                entity.setDepartId(resultSet.getInt("depart_id"));
                                entity.setDepartName(resultSet.getString("departname"));
                                entity.setCreateTime(resultSet.getDate("create_time"));
                                list.add(entity); // 把查询的记录封装到了集合容器中
                            }
                            return list; // 返回查询的结果
                        }
                    },startNo,pageUtils.getPageSize());
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int count(PageUtils pageUtils) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "select count(1) from t_classes ";
        if(StringUtils.isNotEmpty(pageUtils.getKey())){
            sql = "select count(1) from t_classes where name like '%"+pageUtils.getKey()+"%' or notes like '%"+pageUtils.getKey()+"%' ";
        }
        try {
            return queryRunner.query(sql, new ResultSetHandler<Integer>() {
                @Override
                public Integer handle(ResultSet resultSet) throws SQLException {
                    resultSet.next();
                    return resultSet.getInt(1);
                }
            });
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public Classes findById(int id) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "select * from t_classes where id = ?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<Classes>() {
                @Override
                public Classes handle(ResultSet resultSet) throws SQLException {
                    // 存储返回结果的容器
                    if(resultSet.next()){
                    // 每次循环一次 user 存储一条数据
                        Classes entity = new Classes();
                        entity.setId(resultSet.getInt("id"));
                        entity.setName(resultSet.getString("name"));
                        entity.setNotes(resultSet.getString("notes"));
                        entity.setDepartId(resultSet.getInt("depart_id"));
                        entity.setDepartName(resultSet.getString("departname"));
                        entity.setCreateTime(resultSet.getDate("create_time"));
                        return entity;
                    }
                    return null; // 返回查询的结果
                }
            },id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int save(Classes entity) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "insert into t_classes(name,notes,depart_id,departname)values(?,?,?,?)";
        try {
            return queryRunner.update(sql,entity.getName(),entity.getNotes(),entity.getDepartId(),entity.getDepartName());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int updateById(Classes entity) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "update t_classes set name = ?,notes = ?,depart_id = ?,departname = ? where id = ?";
        try {
            return queryRunner.update(sql,entity.getName(),entity.getNotes(),entity.getDepartId(),entity.getDepartName(),entity.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }

    @Override
    public int deleteById(int id) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "delete from t_classes where id = ?";
        try {
            queryRunner.update(sql,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Classes> list(Classes cls) {
        QueryRunner queryRunner = MyDbutils.getQueryRunner();
        String sql = "select * from t_classes where 1 = 1";
        if (cls != null){
            if (cls.getDepartId() != null && cls.getDepartId() > 0){
                sql += " and depart_id = " + cls.getDepartId();
            }
        }
        // 计算 分页开始的位置
        try {
            List<Classes> list = queryRunner.query(sql, new
                    ResultSetHandler<List<Classes>>() {
                        @Override
                        public List<Classes> handle(ResultSet resultSet) throws SQLException {
                            // 存储返回结果的容器
                            List<Classes> list = new ArrayList<>();
                            while(resultSet.next()){
                                // 每次循环一次 user 存储一条数据
                                Classes entity = new Classes();
                                entity.setId(resultSet.getInt("id"));
                                entity.setName(resultSet.getString("name"));
                                entity.setNotes(resultSet.getString("notes"));
                                entity.setDepartId(resultSet.getInt("depart_id"));
                                entity.setDepartName(resultSet.getString("departname"));
                                entity.setCreateTime(resultSet.getDate("create_time"));
                                list.add(entity); // 把查询的记录封装到了集合容器中
                            }
                            return list; // 返回查询的结果
                        }
                    });
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}
